﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class QuotationRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<QuotationModel> GetAllQuotations()
        {
            List<QuotationModel> allQuotations = new List<QuotationModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".quotation WHERE vc_fldValid = 'Y'";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    QuotationModel quotation = new QuotationModel();

                    quotation.QuotationNo = dataReader["vc_fldQuotationNo"] + "";
                    quotation.FMName = dataReader["vc_fldFMName"] + "";
                    quotation.NoOfItems = int.Parse(dataReader["in_fldNoOfItems"] + "");
                    quotation.TotalAmount = decimal.Parse(dataReader["dc_fldTotalAmount"] + "");
                    quotation.SubContName = dataReader["vc_fldSubContCode"] + "";
                    quotation.HasSubContractor = dataReader["vc_fldHasSubContractor"] + "";
                    quotation.SubContAmount = decimal.Parse(dataReader["dc_fldSubContAmount"] + "");
                    quotation.WorkCompletionDate = DateTime.Parse(dataReader["dt_fldWorkCompletion"] + "");
                    quotation.QuotationCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");
                    quotation.IsValid = dataReader["vc_fldValid"] + "";
                    quotation.PONo = dataReader["vc_fldPONo"] + "";


                    allQuotations.Add(quotation);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allQuotations;
        }

        public List<QuotationModel> GetAllQuotations(int startIndex, int count, string sorting, List<QuotationModel> srcList)
        {
            IEnumerable<QuotationModel> allQuotations = srcList;

            //Sorting
            //This ugly code is used just for demonstration.
            //Normally, Incoming sorting text can be directly appended to an SQL query.
            if (string.IsNullOrEmpty(sorting) || sorting.Equals("QuotationNo ASC"))
            {
                allQuotations = allQuotations.OrderBy(p => p.QuotationNo);
            }
            else if (sorting.Equals("QuotationNo DESC"))
            {
                allQuotations = allQuotations.OrderByDescending(p => p.QuotationNo);
            }

            return count > 0
                       ? allQuotations.Skip(startIndex).Take(count).ToList() //Paging
                       : allQuotations.ToList(); //No paging
        }

        public List<QuotationItemModel> GetAllQuotationItems(String quotationNo)
        {
            List<QuotationItemModel> allQuotationItems = new List<QuotationItemModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".quotationitem where vc_fldQuotationNo = '" + quotationNo + "'";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    QuotationItemModel quoteItem = new QuotationItemModel();

                    quoteItem.QuotationNo = dataReader["vc_fldQuotationNo"] + "";
                    quoteItem.SeqNo = int.Parse(dataReader["in_fldSeqNo"] + "");
                    quoteItem.ItemNo = int.Parse(dataReader["in_fldItemNo"] + "");
                    quoteItem.ItemDescription = dataReader["vc_fldItemDescription"] + "";
                    quoteItem.SORItem = dataReader["vc_fldSORItem"] + "";
                    quoteItem.Unit = dataReader["vc_fldUnit"] + "";
                    quoteItem.Qty = int.Parse(dataReader["in_fldQty"] + "");
                    quoteItem.Rate = decimal.Parse(dataReader["dc_fldRate"] + "");
                    quoteItem.ItemAmount = decimal.Parse(dataReader["dc_fldAmount"] + "");
                    quoteItem.QuotationItemCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");
                    quoteItem.IsValid = dataReader["vc_fldIsValid"] + "";

                    allQuotationItems.Add(quoteItem);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allQuotationItems;
        }

        public QuotationModel GetQuotation(String quotationNo)
        {
            QuotationModel foundQuotation = new QuotationModel();

            List<QuotationModel> tempQuotationsList = GetAllQuotations();
            foundQuotation = tempQuotationsList.Find(x => x.QuotationNo== quotationNo);

            return foundQuotation;
        }

        public QuotationModel GetQuotationByPONo(String PONo)
        {
            QuotationModel foundQuotation = new QuotationModel();

            List<QuotationModel> tempQuotationsList = GetAllQuotations();
            foundQuotation = tempQuotationsList.Find(x => x.PONo == PONo);

            return foundQuotation;
        }

        public bool Insert(QuotationModel quotationModel)
        {
            bool isInserted = false;

            try
            {
                object tempWord = "'" + DateTime.MinValue.ToString("yyyy-MM-dd H:mm:ss") + "'";
                if (quotationModel.WorkCompletionDate != null)
                    tempWord = "'" + quotationModel.WorkCompletionDate.Value.ToString("yyyy-MM-dd H:mm:ss") + "'";

                String query = "INSERT INTO " + Configuration.DatabaseName + ".quotation (vc_fldQuotationNo, vc_fldFMName, in_fldNoOfItems, dc_fldTotalAmount, "
                    + "vc_fldSubContCode, vc_fldHasSubContractor, dc_fldSubContAmount, dt_fldWorkCompletion, dt_fldCreate, vc_fldValid, vc_fldPONo) VALUES('" + quotationModel.QuotationNo
                    + "', '" + quotationModel.FMName + "', '" + quotationModel.NoOfItems + "', '" + quotationModel.TotalAmount + "', '" + quotationModel.SubContName + "', '" + quotationModel.HasSubContractor
                    + "', '" + quotationModel.SubContAmount + "'," + tempWord + ", '" + quotationModel.QuotationCreationDate.ToString("yyyy-MM-dd H:mm:ss")
                    + "', '" + quotationModel.IsValid + "', '" + quotationModel.PONo + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public bool UpdateInValidFlag(QuotationModel quotationModel)
        {
            bool isUpdated = false;

            try
            {
                String query = "UPDATE " + Configuration.DatabaseName + ".quotation SET vc_fldValid = 'N' WHERE vc_fldQuotationNo = '" 
                    + quotationModel.QuotationNo + "'";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isUpdated = true;
            }
            catch (Exception e)
            {
                isUpdated = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isUpdated;
        }

        public bool Insert(List<QuotationItemModel> quoteItemModel)
        {
            bool isInserted = false;

            foreach (QuotationItemModel item in quoteItemModel)
            {
                try
                {
                    String query = "INSERT INTO " + Configuration.DatabaseName + ".quotationitem (vc_fldQuotationNo, in_fldSeqNo, in_fldItemNo, vc_fldItemDescription,"
                        + "vc_fldSORItem, vc_fldUnit, in_fldQty, dc_fldAmount, dt_fldCreate, vc_fldIsValid, dc_fldRate) VALUES('" + item.QuotationNo + "', '" + item.SeqNo
                        + "', '" + item.ItemNo + "', '" + item.ItemDescription + "', '" + item.SORItem + "', '" + item.Unit
                        + "', '" + item.Qty + "', '" + item.ItemAmount + "', '" + item.QuotationItemCreationDate.ToString("yyyy-MM-dd H:mm:ss")
                        + "', '" + item.IsValid + "', '" + item.Rate + "')";
                    db.DBConnectionOn();
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, db.connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    isInserted = true;
                }
                catch (Exception e)
                {
                    isInserted = false;
                    throw;
                }
                finally
                {
                    db.DBConnectionOff();
                }
            }

            return isInserted;
        }

        public bool UpdateInValidFlag(List<QuotationItemModel> quoteItemModel)
        {
            bool isUpdated = false;

            foreach (QuotationItemModel item in quoteItemModel)
            {
                try
                {
                    String query = "UPDATE " + Configuration.DatabaseName + ".quotationitem SET vc_fldIsValid = 'N' WHERE vc_fldQuotationNo = '"
                        + item.QuotationNo + "'";
                    db.DBConnectionOn();
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, db.connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    isUpdated = true;
                }
                catch (Exception e)
                {
                    isUpdated = false;
                    throw;
                }
                finally
                {
                    db.DBConnectionOff();
                }
            }

            return isUpdated;
        }

        private List<String> GetQuotationNoList()
        {
            List<String> QuotNoList = new List<string>();

            List<QuotationModel> quotationList = GetAllQuotations();

            if (quotationList.Count > 0)
            {
                foreach (QuotationModel quot in quotationList)
                    QuotNoList.Add(quot.QuotationNo);
            }


            return QuotNoList;
        }

        public String GetNextQuotationNo()
        {
            String quotationNo = "";

            List<String> QuotNoList = GetQuotationNoList();

            if (QuotNoList.Count > 0)
            {
                List<int> tempList = new List<int>();

                // Step 1: Split the quotation no - [0]: FM Name, [1]: Q (Quotation), [2]: YYMM (Year & Month), [3]: xxxxx / xxxxxrx (Real Quotation No)
                foreach (String quotNo in QuotNoList)
                {
                    String[] QuotNoSplit = quotNo.Split('-');
                    String[] QuotNoSplit2 = QuotNoSplit[3].Split('r');

                    tempList.Add(int.Parse(QuotNoSplit2[0]));
                }

                // Step 2: Sort the quotation no in ascending order
                tempList = tempList.OrderBy(v => v).ToList();

                // Step 3: Generate a new quotation no
                int tempNo = tempList[tempList.Count - 1] + 1;
                quotationNo = tempNo.ToString("00000");
            }
            else
                quotationNo = "00001";

            return quotationNo;
        }
    }
}